package com.foreknow.demo14;

import com.foreknow.demo14.User;
import com.foreknow.demo14.demo14Z.DBUtilZy;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBUtilPre {
    private Connection conn;
    private PreparedStatement preparedStatement;
    private ResultSet rs;
    /**
     * 连接数据库
     */
    public Connection getConnection() throws SQLException, ClassNotFoundException {
        //1. 加载驱动程序
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2. 获取连接Connection
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/base23?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC","root","123456");
        //设置为手动提交事务
        conn.setAutoCommit(false);
        return conn;
    }

    /**
     * 查询
     * select * from user
     * select *from user where id=? and username=?
     */
    public ResultSet QueryInfo(String sql,Object...values) throws SQLException {
        preparedStatement = conn.prepareStatement(sql);//select * from user where id=?
        //遍历数组
        for(int i = 0;i<values.length;i++){
            preparedStatement.setObject(i+1,values[i]);
        }
        rs = preparedStatement.executeQuery();
        return rs;
    }


    /**
     * DML(数据操作)
     * insert delete update
     */
    public boolean dml(String sql,Object...values) throws SQLException {
        PreparedStatement pstmt = conn.prepareStatement(sql);//创建一个 PreparedStatement对象，用于将SQL语句发送到数据库。
        //遍历数组
        for (int i = 0; i < values.length; i++) {
            preparedStatement.setObject(i+1,values[i]);
        }
        int isRight = preparedStatement.executeUpdate();
        return isRight == 1;
    }

    /**
     * 关闭连接
     */
    public void closeAll() throws SQLException {
        if(rs!=null) {
            rs.close();
        }
        if(preparedStatement!=null) {
            preparedStatement.close();
        }
        if(conn!=null) {
            conn.close();
        }
    }

    public static void main(String[] args) {
        DBUtilPre dbUtil = new DBUtilPre();
        try {
            //连接数据库
            Connection conn = dbUtil.getConnection();
            ResultSet rs = dbUtil.QueryInfo("select * from user where id=?",4);
//            List<User> list = new ArrayList<>();
//            while (rs.next()) {
//                //获取到当前行数据(第一列，第二列.....第n列)
//                int id = rs.getInt("id"); //获取到第一列的值
//                String username = rs.getString("username");//获取到第二列的值
//                String password = rs.getString("password");//获取到第三列的值
//                //System.out.println(id+"---"+username+"---"+password);
//                User user = new User();
//                user.setId(id);
//                user.setUsername(username);
//                user.setPassword(password);
//                //将对象保存到集合中
//                list.add(user);
//            }
//            for (User user:list) {
//                System.out.println(user.getUsername());
//            }
//            boolean isRigth = dbUtil.dml("INSERT INTO user(username,password) VALUES(?,?)","kkk123","123456");
            boolean isRigth = dbUtil.dml("INSERT INTO user(username,password) VALUES(?,?)","kkk123","123456");
            //提交事务
            conn.commit();
            System.out.println(isRigth);
//            boolean isRigth = dbUtil.dml("INSERT INTO user(username,password) VALUES('xy123','111111')");
//            if(isRigth) {
//                System.out.println("添加成功......");
//            }else {
//                System.out.println("添加失败......");
//            }
//            dbUtil.closeAll();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
